package com.dp.appi.dao.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.dp.appi.dao.ExplorationDao;
import com.dp.appi.dao.PackageDao;
import com.dp.appi.exception.CommonException;
import com.dp.appi.util.PointUtil;
import com.dp.appi.util.SYS;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import java.math.BigDecimal;
import java.sql.*;
import java.text.DecimalFormat;
import java.util.*;

/**
 * Created by xiaoji on 2016/5/9.
 * 踏勘点数据层
 */
@Component
public class ExplorationDaoImpl extends BaseDaoImpl implements ExplorationDao {
    /**
     * 获取踏勘数据类别
     *
     * @param stage_id 阶段id
     * @param id       当前用户
     * @return
     */
    @Override
    public Integer getClassify(Long stage_id, String id) {
        String sql = "select * from p_stage where stage_id=? and deleted=0";
        Map<String, Object> result = findOneObject(sql, new Object[]{stage_id});
        if (result == null) {
            throw new CommonException(SYS.EXPO003.name());
        }
        //当前进度
        int current_schedule = Integer.parseInt(String.valueOf(result.get("current_schedule")));
        //用户权限
        String manager = String.valueOf(result.get("manager"));
        //项目踏勘人
        String explorationer = String.valueOf(result.get("explorationer"));
        //设计人
        String designer = String.valueOf(result.get("designer"));
        //审核人
        String auditor = String.valueOf(result.get("auditor"));
        //当前进度==踏勘

        int k = manager.indexOf("ff808081567e3a2c01569c78543900ad");
        if (current_schedule == 11) {
            return 1;
        } else if (manager.indexOf(id) != -1
                || designer.indexOf(id) != -1 || auditor.indexOf(id) != -1) {//负责人+设计人+审核人 + 非踏勘
            return 2;
        } else {//紧为踏勘人+非踏勘
            return 1;
        }
    }

    /**
     * 获取踏勘点列表
     *
     * @param exploration_type 踏勘类型(新建11 / 拆旧12)
     * @param stage_id         项目阶段id
     * @param classify         数据类型
     * @return
     */
    @Override
    public List<Map<String, Object>> explorationList(Integer exploration_type, Long stage_id, Integer classify, Long explor_id) {
        List list = new ArrayList();
        StringBuffer sql = new StringBuffer();
        sql.append("select pe.exploration_id,pe.uuid,pe.number,pe.codeset_id,pe.before_exp,pe.pt_id,ppt.pt_type,pe.pt_objectid,");
        sql.append("pe.lng,pe.lat,pe.alt,pe.distance,pe.corner,pex.corner as before_corner,ps.current_schedule as schedule_id,pe.location_way,aup.user_name,");
        sql.append("pe.explor_id,pe2.lng as before_lng,pe2.lat as before_lat,pe.voltage_grade,pe.exploration_person ");
        sql.append(" from p_exploration pe");
        sql.append(" LEFT JOIN p_pt ppt on pe.pt_id=ppt.pt_id ");
        sql.append(" LEFT JOIN p_stage ps on pe.stage_id=ps.stage_id");
        sql.append(" LEFT JOIN a_user_point aup on (aup.id=pe.pt_objectid and pe.pt_id=100005)");
        sql.append(" LEFT JOIN p_exploration pe2 ON (pe.before_exp=pe2.exploration_id and pe.before_exp!=0 and pe2.deleted=0)");
        sql.append(" LEFT JOIN p_exploration pex on (pe.before_exp = pex.exploration_id and pex.deleted=0)");
        sql.append(" where pe.deleted=0 and ps.deleted=0 ");
        sql.append(" and pe.stage_id=? and pe.classify=? ");

        list.add(stage_id);
        list.add(classify);
        if (null != exploration_type) {
            list.add(exploration_type);
            sql.append(" and pe.exploration_type=? ");
        }

        if (null != explor_id) {
            if (explor_id == -1) {
                list.add(101);
                sql.append(" and (pe.voltage_grade=?) ");
            } else {
                list.add(explor_id);
                sql.append(" and (pe.explor_id=?) ");
            }
        }
        sql.append(" order by pe.exploration_id desc  ");
        list = getJdbcTemplate().queryForList(sql.toString(), list.toArray());
        return list ;

    }

    /**
     * 10kv踏勘点列表
     *
     * @param exploration_type
     * @param stage_id
     * @param classify
     * @return
     */
    @Override
    public List<Map<String, Object>> explorationList_high(String exploration_type, Long stage_id, Integer classify) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select pex.exploration_id,pex.number,pt.`name`,pex.lng,pex.lat from p_exploration pex");
        sb.append(" INNER JOIN p_pt pt on pex.pt_id = pt.pt_id");
        sb.append("  where pex.voltage_grade = 101");
        sb.append(" and pt.pt_id = 100004");
        sb.append(" and pex.deleted=0");
        sb.append(" and pex.exploration_type = ?");
        sb.append(" and pex.stage_id = ?");
        sb.append(" and pex.classify = ?");
        return findForJdbc(sb.toString(), new Object[]{exploration_type, stage_id, classify});
    }

    /**
     * 是否具有操作踏勘点的权限
     *
     * @param exploration_id 踏勘类型
     * @param id             当前用户id
     * @return
     */
    @Override
    public boolean isDelExplor(Long exploration_id, String id) {
        String sql = "select * from p_exploration pe" +
                " LEFT JOIN p_stage pst ON pe.stage_id=pst.stage_id" +
                " where pe.deleted=0 and pst.deleted=0 AND pe.exploration_id=? and ((pst.explorationer like ? and pe.classify=1)" +
                " or ((pst.manager like ?   or pst.designer like ? or pst.auditor like ?) and pe.classify=2))";
        String str = "%" + id + "%";
        Map<String, Object> result = findOneObject(sql, new Object[]{exploration_id, str, str, str, str});
        if (result != null) {
            return true;
        }
        return false;
    }

    @Autowired
    private PackageDao packageDao;

    /**
     * 删除踏勘点
     *
     * @param exploration_id 踏勘点id
     */
    @Override
    public void

    delExplor(Long exploration_id) {
        String sql = "update p_exploration set deleted=1 where exploration_id=?";
        getJdbcTemplate().update(sql, new Object[]{exploration_id});


        //当前点的上联信息
        StringBuilder sb = new StringBuilder();
        sb.append("  select pex.exploration_id,pex.lng,pex.lat,pex.before_exp from p_exploration pe");
        sb.append("  inner join p_exploration pex on pex.exploration_id = pe.before_exp");
        sb.append(" where pe.exploration_id = ?");
        sb.append(" and pex.deleted = 0");
        Map<String, Object> mapp = findOneObject(sb.toString(), new Object[]{exploration_id});

        Object point_befor;
        Long point_id = null;
        Double point_lng = null;
        Double point_lat = null;
        Double up_lng = null;
        Double up_lat = null;
        Double corner = null;

        String sql_update_corner = "update p_exploration set corner=? where exploration_id =?";

        if (null != mapp) {
            //当前点上联点位角度重新计算
            point_id = Long.valueOf(mapp.get("exploration_id").toString());//当前点位的id
            point_lng = Double.valueOf(mapp.get("lng").toString());
            point_lat = Double.valueOf(mapp.get("lat").toString());
            point_befor = mapp.get("before_exp");//当前点位的上联id


            if (point_befor == null) {//上联为空
                executeSql(sql_update_corner, new Object[]{null, point_id});
            } else {
                //上联点位信息
                Long point_befor_id = Long.valueOf(point_befor.toString());
                String sql_up = "select * from p_exploration px WHERE px.exploration_id = ?";
                Map<String, Object> map = findOneForJdbc(sql_up, new Object[]{point_befor_id});
                up_lng = Double.valueOf(map.get("lng").toString());
                up_lat = Double.valueOf(map.get("lat").toString());

                //点位下联对象集合
                String sql_down = "SELECT t1.* from p_exploration as t1 inner join p_pt as t2 on t1.pt_id=t2.pt_id " +
                        "where t1.before_exp = ? and t2.pt_type=11 and t1.deleted = 0";
                List<Map<String, Object>> List_down = findForJdbc(sql_down, new Object[]{point_id});
                if (List_down.isEmpty()) {//下联为空
                    executeSql(sql_update_corner, new Object[]{null, point_id});
                } else {
                    if (List_down.size() == 1) {//下联数量为1
                        Double down_lng = Double.valueOf(List_down.get(0).get("lng").toString());
                        Double down_lat = Double.valueOf(List_down.get(0).get("lat").toString());

                        double distance_point_up = PointUtil.GetLongDistance(point_lng, point_lat, up_lng, up_lat);
                        double distance_point_down = PointUtil.GetLongDistance(point_lng, point_lat, down_lng, down_lat);
                        double distance_up_down = PointUtil.GetLongDistance(up_lng, up_lat, down_lng, down_lat);

                        double a = Math.pow(distance_point_up, 2) + Math.pow(distance_point_down, 2) - Math.pow(distance_up_down, 2);
                        double b = 2 * distance_point_up * distance_point_down;

                        double k = a / b;
                        if (k > 1)
                            k = 1;
                        else if (k < -1)
                            k = -1;

                        corner = Math.acos(k) * 180 / Math.PI;
//                        corner = Math.acos((Math.pow(distance_point_up, 2) + Math.pow(distance_point_down, 2) - Math.pow(distance_up_down, 2)) / (2 * distance_point_up * distance_point_down))*180/Math.PI;
                        if (corner > 90) {
                            corner = 180 - corner;
                        }
                        executeSql(sql_update_corner, new Object[]{corner, point_id});
                    } else {
                        executeSql(sql_update_corner, new Object[]{null, point_id});
                    }

                }
                //更新当前点位的所有下联点位台区标识
                for (int j = 0; j < List_down.size(); j++) {
                    this.updateExp_id(Long.valueOf(String.valueOf(List_down.get(j).get("exploration_id"))), exploration_id);
                }

            }
        } else {
            //点位下联对象集合
            String sql_down = "SELECT t1.* from p_exploration as t1 inner join p_pt as t2 on t1.pt_id=t2.pt_id " +
                    "where t1.before_exp = ? and t2.pt_type=11 and t1.deleted = 0";
            List<Map<String, Object>> List_down = findForJdbc(sql_down, new Object[]{point_id});
            if (List_down.isEmpty()) {//下联为空
                executeSql(sql_update_corner, new Object[]{null, point_id});
            } else {
                if (List_down.size() == 1) {//下联数量为1
                    Double down_lng = Double.valueOf(List_down.get(0).get("lng").toString());
                    Double down_lat = Double.valueOf(List_down.get(0).get("lat").toString());

                    double distance_point_up = PointUtil.GetLongDistance(point_lng, point_lat, up_lng, up_lat);
                    double distance_point_down = PointUtil.GetLongDistance(point_lng, point_lat, down_lng, down_lat);
                    double distance_up_down = PointUtil.GetLongDistance(up_lng, up_lat, down_lng, down_lat);

                    double a = Math.pow(distance_point_up, 2) + Math.pow(distance_point_down, 2) - Math.pow(distance_up_down, 2);
                    double b = 2 * distance_point_up * distance_point_down;

                    double k = a / b;
                    if (k > 1)
                        k = 1;
                    else if (k < -1)
                        k = -1;

                    corner = Math.acos(k) * 180 / Math.PI;
//                        corner = Math.acos((Math.pow(distance_point_up, 2) + Math.pow(distance_point_down, 2) - Math.pow(distance_up_down, 2)) / (2 * distance_point_up * distance_point_down))*180/Math.PI;
                    if (corner > 90) {
                        corner = 180 - corner;
                    }
                    executeSql(sql_update_corner, new Object[]{corner, point_id});
                } else {
                    executeSql(sql_update_corner, new Object[]{null, point_id});
                }
            }
        }

            //更新当前点位的下联点位属性中角度和档距为null
            String sql_2 = "select exploration_id,lng,lat,before_exp from p_exploration where before_exp = ? and deleted = 0";
            List<Map<String, Object>> list = findForJdbc(sql_2, new Object[]{exploration_id});
            if (list != null) {
                String up_sql = "update p_exploration set corner = NULL,distance=NULL,before_exp = NULL where exploration_id = ?";
                for (int i = 0; i < list.size(); i++) {
                    executeSql(up_sql, new Object[]{list.get(i).get("exploration_id")});
                }
            }

    }

    /**
     * 获取踏勘点信息【app端获取点位信息】
     *
     * @param exploration_id
     * @return
     */
    @Override
    public Map<String, Object> explorInfo(Long exploration_id) {
        String sql = "select pe.exploration_id,pe.uuid,pe.number,pe1.exploration_id before_exp,pe1.number before_number,pe.distance,pe.exploration_type,pe.pt_id,pe.pt_objectid,pe.lng,pe.voltage_grade," +
                "pe.lat,pe.alt,pe.codeset_id,cc.number codeset_number,cc.`name` codeset_name,pe.corner,pe1.corner as before_corner,pe.location_way  " +
                " from p_exploration pe" +
                " LEFT JOIN c_codeset cc" +
                " on pe.codeset_id = cc.codeset_id" +
                " LEFT JOIN p_exploration pe1" +
                " on pe.before_exp = pe1.exploration_id and pe1.deleted=0" +
                " where pe.exploration_id=? and pe.deleted=0";
        return findOneObject(sql, new Object[]{exploration_id});
    }

    /**
     * 获取踏勘点信息
     *
     * @param exploration_id
     * @return
     */
    @Override
    public Map<String, Object> explorBaseInfo(Long exploration_id) {
        String sql = "select * from p_exploration pe" +
                " where pe.exploration_id=? and pe.deleted=0";
        return getJdbcTemplate().queryForObject(sql, getColumnMapRowMapper(), exploration_id);
//        Map<String, Object> map = queryForMap(sql, exploration_id);
//        return map;
    }

    /**
     * 获取点信息【app端获取信息】
     *
     * @param id    信息id
     * @param pt_id 点类型id
     * @return
     */
    @Override
    public Map<String, Object> pointInfo(Long id, Long pt_id) {
        String sql = "select * from p_pt where pt_id=? and is_deleted=0 and is_valid=1";
        Map<String, Object> map = queryForMap(sql, pt_id);
        if (map != null) {
            //SELECT * from a_pole where id=?;
            String sql2 = "select * from " + map.get("pt_table") + " where id=?";
            Map<String, Object> map2 = queryForMap(sql2, id);
            return map2;
        }
        return null;
    }


    /**
     * 踏勘点信息设置
     *
     * @param param 踏勘点参数
     * @param id    当前用户id
     */
    @Override
    public Long addExploration(final Map<String, String> param, final String id) {
        final String sql = "INSERT INTO p_exploration(number,before_exp,exploration_person,exploration_type," +
                "pt_id,pt_objectid,lng,lat,distance,alt,location_way,create_time,explor_id," +
                "exp_input,classify,stage_id,voltage_grade) VALUES (?,?,?,?,?,?,?,?,?,?,?,NOW(),?,1,?,?,?)";
        KeyHolder key = new GeneratedKeyHolder();
        getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, param.get("number"));
                String before_exp = param.get("before_exp");
                if (StringUtils.isEmpty(before_exp)) {
                    ps.setNull(2, Types.BIGINT);
                } else {
                    ps.setLong(2, Long.parseLong(before_exp));
                }
                ps.setString(3, id);
                ps.setInt(4, Integer.parseInt(param.get("exploration_type")));
                ps.setLong(5, Long.parseLong(param.get("pt_id")));
                if (StringUtils.isEmpty(param.get("pt_objectid"))) {
                    ps.setNull(6, Types.BIGINT);
                } else {
                    ps.setLong(6, Long.parseLong(param.get("pt_objectid")));//生成踏勘点信息后Id
                }
                ps.setBigDecimal(7, new BigDecimal(param.get("lng")));
                ps.setBigDecimal(8, new BigDecimal(param.get("lat")));
                if (StringUtils.isEmpty(param.get("distance"))) {
                    ps.setNull(9, Types.FLOAT);
                } else {
                    ps.setFloat(9, Float.parseFloat(param.get("distance")));//?
                }

                if (StringUtils.isEmpty(param.get("alt"))) {
                    ps.setNull(10, Types.DECIMAL);
                } else {
                    ps.setBigDecimal(10, new BigDecimal(param.get("alt")));
                }

                ps.setInt(11, Integer.parseInt(param.get("location_way")));
                if (StringUtils.isEmpty(before_exp)) {
                    ps.setNull(12, Types.BIGINT);
                } else {
                    //如果pt_id是变压器，则explor_id暂时为空，返回null
                    if (Long.parseLong(param.get("pt_id")) == 100002) {
                        ps.setNull(12, Types.BIGINT);
                    } else {
                        Long temp = getExplorIdByBeforeExp(Long.parseLong(before_exp), new ArrayList<Long>());
                        if (temp == null) {
                            ps.setNull(12, Types.BIGINT);
                        } else {
                            ps.setLong(12, temp);//? 所属台区上一级的变压器踏勘id
                        }
                    }
                }
                ps.setInt(13, Integer.parseInt(param.get("classify")));
                ps.setLong(14, Long.parseLong(param.get("stage_id")));
                if (StringUtils.isEmpty(param.get("voltage_grade"))) {
                    ps.setNull(15, Types.INTEGER);
                } else {
                    ps.setInt(15, Integer.parseInt(param.get("voltage_grade")));
                }
                return ps;
            }
        }, key);
        long id2 = key.getKey().longValue();

        //台区设置为自己的id
        if (Long.parseLong(param.get("pt_id")) == 100002) {
            StringBuffer upSql = new StringBuffer();
            upSql.append("update p_exploration set explor_id= ? where exploration_id=?");
            getJdbcTemplate().update(upSql.toString(), id2, id2);
        }

        //更新上级转角
        upBeforeExpCorner(param.get("before_exp"), param.get("corner"));

        return id2;
    }

    /**
     * 更新转角
     *
     * @param exp_id
     * @param corner
     */
    @Override
    public void upBeforeExpCorner(Object exp_id, Object corner) {
        if (!StringUtils.isEmpty(exp_id)) {
            StringBuffer upSql = new StringBuffer();
            upSql.append("update p_exploration set corner= ? where exploration_id=?");
            getJdbcTemplate().update(upSql.toString(), corner, exp_id);
        }
    }


/*
     修改杆塔信息上联，同时修改交叉跨院的上联
 */
    @Override
    public void upjckyExploration(Long exploration_id, Long new_before_exp_id) {
        StringBuffer upSql = new StringBuffer();
        upSql.append("UPDATE p_exploration set before_exp= ? where exploration_id in( SELECT feature_id from p_channel where exploration_id=  ? )");
        getJdbcTemplate().update(upSql.toString(), new_before_exp_id, exploration_id);
   }
    /**
     * 根据上一级踏勘点id获取变压器id(所属台区)
     *
     * @param
     * @return
     */

        @Override
    public Long getExplorIdByBeforeExp(Long beforeExp, List<Long> be) {
        if (beforeExp != null) {
            be.add(beforeExp);
            String sql = "select * from p_exploration where exploration_id=?";
            Map<String, Object> result = findOneObject(sql, new Object[]{beforeExp});
            if (result != null) {
                Object bExp = result.get("explor_id");
                if (!StringUtils.isEmpty(bExp)) {//上一级的台区不为空，则下级继承上级台区
                    Long tq = Long.valueOf(String.valueOf(bExp));
                    String usql = "update p_exploration set explor_id= ? where exploration_id=?";
                    for (Long b : be) {
                        getJdbcTemplate().update(usql, tq, b);
                    }
                    be.clear();
                    return tq;
                }
                long pt_id = Long.parseLong(String.valueOf(result.get("pt_id")));
                if (pt_id == 100002) {//如果是变压器返回
                    be.clear();
                    return Long.parseLong(String.valueOf(result.get("exploration_id")));
                } else {//否则递归
                    Object beforE = result.get("before_exp");
                    if (beforE == null) {
                        return null;
                    }
                    beforeExp = Long.parseLong(String.valueOf(beforE));
                    return getExplorIdByBeforeExp(beforeExp, be);
                }
            } else {
                be.clear();
                return null;
            }
        } else {
            //第一个 没有上联的时候
            return null;
        }
    }

    /**
     * 获得计算后角度
     *
     * @param lng
     * @param lat
     * @param befor_id
     * @return
     */
    @Override
    public Double returnCorner(Long exploration_id, double lng, double lat, Long befor_id, String type) {
        //选择的上联对象
        Double corner = null;
        String sql_obj = "select * from p_exploration where exploration_id = ?";
        Map<String, Object> before_obj = this.findOneObject(sql_obj, new Object[]{befor_id});

        //选择的上联的上联主键
        Object before_before_id = before_obj.get("before_exp");

        //查询选择的上联是否还有其他的分支
        String sql_fz = "select count(*) as countNumber  from p_exploration where before_exp = ? and deleted = 0";
        Map<String, Object> before_fz_count = this.findOneObject(sql_fz, new Object[]{befor_id});
        if (null != before_before_id) {
            if (Integer.valueOf(type) == 0) {
                if (Integer.valueOf(before_fz_count.get("countNumber").toString()) == 0) {
//                    选择的上联的上联
                    Map<String, Object> before_before_obj = this.findOneObject(sql_obj, new Object[]{before_before_id});
                    Double lng_a = Double.valueOf(before_before_obj.get("lng").toString());
                    Double lat_a = Double.valueOf(before_before_obj.get("lat").toString());

                    //选择的上联
                    Double lng_b = Double.valueOf(before_obj.get("lng").toString());
                    Double lat_b = Double.valueOf(before_obj.get("lat").toString());

                    //设上联的上联为点a，上联为点b，当前新踏勘点为c
                    double distance_point_up = PointUtil.GetLongDistance(lng_a, lat_a, lng_b, lat_b);//a——b距离
                    double distance_point_down = PointUtil.GetLongDistance(lng_b, lat_b, lng, lat);//b——c距离
                    double distance_up_down = PointUtil.GetLongDistance(lng_a, lat_a, lng, lat);//a——c距离
                    double a = Math.pow(distance_point_up, 2) + Math.pow(distance_point_down, 2) - Math.pow(distance_up_down, 2);
                    double b = 2 * distance_point_up * distance_point_down;
                    double k;
                    if (String.valueOf(b).equals("0.0")) {
                        corner = 0.0;
                    } else {
                        k = a / b;
                        if (k > 1)
                            k = 1;
                        else if (k < -1)
                            k = -1;
                        corner = Math.acos(k) * 180 / Math.PI;
                        if (corner > 90) {
                            corner = 180 - corner;
                        }
                    }

                    BigDecimal cc = new BigDecimal(corner);
                    corner = cc.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
                }
            } else if (Integer.valueOf(type) == 1) {
                String sql_obj2 = " select pex1.exploration_id,pex1.before_exp,pex2.corner from p_exploration pex1 " +
                        " LEFT JOIN p_exploration pex2 on pex1.before_exp = pex2.exploration_id " +
                        " where pex1.exploration_id = ? ";
                Map<String, Object> before_obj2 = this.findOneObject(sql_obj2, new Object[]{exploration_id});
                Long old_before_id = Long.valueOf(before_obj2.get("before_exp").toString());
                if (String.valueOf(old_before_id).equals(String.valueOf(befor_id))) {
                    Object corner2 = before_obj2.get("corner");
                    if (null != corner2)
                        corner = Double.valueOf(String.valueOf(corner2));
                } else {
                    if (Integer.valueOf(before_fz_count.get("countNumber").toString()) == 0) {
//                    选择的上联的上联
                        Map<String, Object> before_before_obj = this.findOneObject(sql_obj, new Object[]{before_before_id});
                        Double lng_a = Double.valueOf(before_before_obj.get("lng").toString());
                        Double lat_a = Double.valueOf(before_before_obj.get("lat").toString());

                        //选择的上联
                        Double lng_b = Double.valueOf(before_obj.get("lng").toString());
                        Double lat_b = Double.valueOf(before_obj.get("lat").toString());

                        //设上联的上联为点a，上联为点b，当前新踏勘点为c
                        double distance_point_up = PointUtil.GetLongDistance(lng_a, lat_a, lng_b, lat_b);//a——b距离
                        double distance_point_down = PointUtil.GetLongDistance(lng_b, lat_b, lng, lat);//b——c距离
                        double distance_up_down = PointUtil.GetLongDistance(lng_a, lat_a, lng, lat);//a——c距离
                        double a = Math.pow(distance_point_up, 2) + Math.pow(distance_point_down, 2) - Math.pow(distance_up_down, 2);
                        double b = 2 * distance_point_up * distance_point_down;
                        double k;
                        if (String.valueOf(b).equals("0.0")) {
                            corner = 0.0;
                        } else {
                            k = a / b;
                            if (k > 1)
                                k = 1;
                            else if (k < -1)
                                k = -1;
                            corner = Math.acos(k) * 180 / Math.PI;
                            if (corner > 90) {
                                corner = 180 - corner;
                            }
                        }

                        BigDecimal cc = new BigDecimal(corner);
                        corner = cc.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
                    }
                }
            }
        }

        return corner;
    }


    /**
     * app排直功能并返回变更后的经纬度
     *
     * @param lng
     * @param lat
     * @param befor_id
     * @return
     */
    @Override
    public Map<String, Object> exp_obj(double lng, double lat, Long befor_id) {
        String sql_obj = "select * from p_exploration where exploration_id = ?";

        //选择的上联
        Map<String, Object> before_obj = this.findOneObject(sql_obj, new Object[]{befor_id});
        Object before_before_id = before_obj.get("before_exp");
        Double lng_b = Double.valueOf(before_obj.get("lng").toString());
        Double lat_b = Double.valueOf(before_obj.get("lat").toString());

        //选择的上联的上联
        Map<String, Object> before_before_obj = this.findOneObject(sql_obj, new Object[]{before_before_id});
        Double lng_a = Double.valueOf(before_before_obj.get("lng").toString());
        Double lat_a = Double.valueOf(before_before_obj.get("lat").toString());

        //设上联的上联为点a，上联为点b，当前新踏勘点为c
        double distance_point_up = PointUtil.GetLongDistance(lng_a, lat_a, lng_b, lat_b);//a——b距离
        double distance_point_down = PointUtil.GetLongDistance(lng_b, lat_b, lng, lat);//b——c距离

        Double all_distance = distance_point_up + distance_point_down;

        //坐标更新计算
        double bz_distance = all_distance / distance_point_up;
        double cz_x = lng_b - lng_a;
        double cz_y = lat_b - lat_a;
        Double lng_up = bz_distance * cz_x + lng_a;
        Double lat_up = bz_distance * cz_y + lat_a;

        Map exp_obj = new HashMap();
        exp_obj.put("lng", lng_up);
        exp_obj.put("lat", lat_up);

        return exp_obj;
    }

    /**
     * 杆塔点位属性设置为共杆
     *
     * @param exploration_id
     */
    @Override
    public Long update_or_save_pole(Long exploration_id) {
        Long id2;
        final String sql = "select exploration_id,number,pt_id,pt_objectid from p_exploration where exploration_id =?";
        Map<String, Object> map = findOneObject(sql, new Object[]{exploration_id});
        Object pt_objectid = map.get("pt_objectid");

        //杆塔点位的详细设计信息
        if (pt_objectid != null && !pt_objectid.equals("")) {
            String sql_2 = "update a_pole set is_common=1 where id =?";
            getJdbcTemplate().update(sql_2, new Object[]{pt_objectid});
            id2 = Long.valueOf(pt_objectid.toString());

        } else {
            final String sql_3 = "insert into a_pole(is_commo,height) VALUES(?,?)";
            KeyHolder key = new GeneratedKeyHolder();
            getJdbcTemplate().update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(sql_3, Statement.RETURN_GENERATED_KEYS);
                    ps.setLong(1, 1);
                    ps.setLong(2, 11);
                    return ps;
                }
            }, key);
            id2 = key.getKey().longValue();
//            String sql_4 = "update p_exploration set pt_objectid = ? where exploration_id = ?";
//            getJdbcTemplate().update(sql_4, new Object[]{exploration_id, pt_objectid});
        }
        return id2;
    }


    /** 点位重新选择上联后下联中所有点位的台区标识更新
     *
     * @param exploration_id 当前点位id
     * @param before_exp_id  选择的上联点位id
     */
    @Override
    public void updateExp_id(Long exploration_id, Long before_exp_id) {
        String sql = "select  pex.pt_id, pex.explor_id from  p_exploration pex where exploration_id =?";
        Map<String, Object> map = findOneObject(sql, new Object[]{before_exp_id});
        Object explor_id = map.get("explor_id");
        //上联存在台区标识
        if (null != explor_id) {
            String sql2 = "update p_exploration set explor_id=? where exploration_id=?";
            this.getJdbcTemplate().update(sql2, new Object[]{explor_id, exploration_id});

            String sql3 = "select pex.exploration_id from p_exploration pex where pex.before_exp = ? and deleted=0 and pex.exploration_type=11";
            List<Map<String, Object>> list = findForJdbc(sql3, new Object[]{exploration_id});

            for (int i = 0; i < list.size(); i++) {
                exploration_id = Long.valueOf(String.valueOf(list.get(i).get("exploration_id")));
                this.getJdbcTemplate().update(sql2, new Object[]{explor_id, exploration_id});

                list = findForJdbc(sql3, new Object[]{exploration_id});
            }
        }
    }

    @Override
    public void updateNum(String num,Object stage_id, Object pt_id) {
        StringBuffer pSql = new StringBuffer();
        pSql.append("select project_id from p_stage where stage_id = ?");
        Long project_id = queryForObject(pSql.toString(), Long.class, stage_id);

        String sql = "select LENGTH(prefix)+1  as headLength from p_pt where pt_id = ?";
        Integer headLength = getJdbcTemplate().queryForObject(sql, Integer.class, pt_id);
        String current_num = num.substring(headLength,num.length());

        StringBuffer uSql = new StringBuffer();
        uSql.append("update p_exploration_num set current_num = ?");
        uSql.append("where project_id = ? and pt_id = ? ");
        getJdbcTemplate().update(uSql.toString(), new Object[]{current_num, project_id, pt_id});
    }

    /**
     * 修改通道特征点距离上级距离
     *
     * @param exploration_id
     * @param distance
     */
    @Override
    public void upExpDistance(Long exploration_id, Float distance) {
        StringBuffer uSql = new StringBuffer();
        uSql.append("update p_exploration set distance = ? ");
        uSql.append("where exploration_id = ?  ");
        getJdbcTemplate().update(uSql.toString(), new Object[]{distance,exploration_id });
    }


    /**
     * 添加搭火点
     *
     * @param param 搭火点属性
     */
    @Override
    public Long addWiring(final Map<String, String> param) {
        final String sql = "INSERT INTO a_wiring(name,wiring_way) VALUES(?,?)";
        KeyHolder key = new GeneratedKeyHolder();
        getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, param.get("name"));
                ps.setInt(2, (int) Double.parseDouble(param.get("wiring_way")));
                return ps;
            }
        }, key);
        long id = key.getKey().longValue();
        return id;
    }

    @Override
    public Long addBj(final Map<String, String> param) {
        String delete="DELETE FROM a_bj where bx_id = "+param.get("bx_id").toString();
        getJdbcTemplate().update(delete);

        final String sql = "INSERT INTO a_bj(code,name,address,type,bx_code,bj_code,factory_code,phone,old_count," +
                "bx_new_code,factory_new_code,t_jx,new_count,new_factory,h,l,bx_id,proId,num,new_num,factory_name,status) " +
                "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        KeyHolder key = new GeneratedKeyHolder();
        String bjlist=param.get("bjList");
        final JSONArray jsonArray= JSON.parseArray(bjlist);
        for(int i=0;i<jsonArray.size();i++){
            final JSONObject jsonObject=jsonArray.getJSONObject(i);
            getJdbcTemplate().update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, jsonObject.getString("code"));
                    ps.setString(2, jsonObject.getString("name"));
                    ps.setString(3, jsonObject.getString("address"));
                    ps.setString(4, jsonObject.getString("type"));
                    ps.setString(5, jsonObject.getString("bx_code"));
                    ps.setString(6, jsonObject.getString("bj_code"));
                    ps.setString(7, jsonObject.getString("factory_code"));
                    ps.setString(8, jsonObject.getString("phone"));
                    ps.setString(9, jsonObject.getString("old_count"));
                    ps.setString(10, jsonObject.getString("bx_new_code"));
                    ps.setString(11, jsonObject.getString("factory_new_code"));
                    ps.setString(12, jsonObject.getString("t_jx"));
                    ps.setString(13, jsonObject.getString("new_count"));
                    ps.setString(14, jsonObject.getString("new_factory"));
                    ps.setString(15, jsonObject.getString("h"));
                    ps.setString(16, jsonObject.getString("l"));
                    ps.setString(17, jsonObject.getString("bx_id"));
                    ps.setString(18, jsonObject.getString("proId"));
                    ps.setString(19, jsonObject.getString("num"));
                    ps.setString(20, jsonObject.getString("new_num"));
                    ps.setString(21, jsonObject.getString("factory_name"));
                    ps.setString(22, jsonObject.getString("status"));
                    return ps;
                }
            }, key);
        }

        long id = 1;
        return id;
    }

    /**
     * 添加柱上变压器
     *
     * @param param 柱上变压器属性
     * @return
     */
    @Override
    public Long addTransformer(final Map<String, String> param) {
        final String sql = "INSERT INTO a_transformer(name,trans_attribute,trans_capacity,trans_type) VALUES(?,?,?,?)";
        KeyHolder key = new GeneratedKeyHolder();
        getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, param.get("name"));
                ps.setInt(2, (int) Double.parseDouble(param.get("trans_attribute")));
                ps.setInt(3, (int) Double.parseDouble(param.get("trans_capacity")));
                ps.setInt(4, (int) Double.parseDouble(param.get("trans_type")));
                return ps;
            }
        }, key);
        long id = key.getKey().longValue();
        return id;
    }

    /**
     * 杆塔
     *
     * @param param 杆塔属性
     * @return
     */
    @Override
    public Long addPole(final Map<String, String> param, final String voltage_grade) {
//        final String sql = "INSERT INTO a_pole(pole_attribute,is_common,pole_type," +
//                "height,num,pole_material,high_wire_type,high_wire_model,high_wire_times," +
//                "low_wire_type,low_wire_model,low_wire_times,rank_way,pole_device," +
//                "corner,span,voltage_type) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        String sql = "";
//        if (voltage_grade.equals("101")) {
        sql = "INSERT INTO a_pole(pole_attribute,is_common,pole_type," +
                "height,num,pole_material,high_wire_type,high_wire_model,high_wire_times,low_wire_type,low_wire_model,low_wire_times," +
                "rank_way,pole_device," +
                "corner,span,voltage_type,laxian) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
//        }
//        else {
//            sql = "INSERT INTO a_pole(pole_attribute,is_common,pole_type," +
//                    "height,num,pole_material," +
//                    "low_wire_type,low_wire_model,low_wire_times,rank_way,pole_device," +
//                    "corner,span,voltage_type) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
//        }
        KeyHolder key = new GeneratedKeyHolder();
        final String finalSql = sql;
        getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(finalSql, Statement.RETURN_GENERATED_KEYS);
                if (StringUtils.isEmpty(param.get("pole_attribute"))) {
                    ps.setNull(1, Types.INTEGER);
                } else {
                    ps.setInt(1, (int) Double.parseDouble(param.get("pole_attribute")));
                }

                if (StringUtils.isEmpty(param.get("is_common"))) {
                    ps.setNull(2, Types.INTEGER);
                } else {
                    ps.setInt(2, (int) Double.parseDouble(param.get("is_common")));
                }

                if (StringUtils.isEmpty(param.get("pole_type"))) {
                    ps.setNull(3, Types.INTEGER);
                } else {
                    ps.setInt(3, (int) Double.parseDouble(param.get("pole_type")));
                }
                if (StringUtils.isEmpty(param.get("height"))) {
                    ps.setNull(4, Types.FLOAT);
                } else {
                    ps.setFloat(4, Float.parseFloat(param.get("height")));
                }

                if (StringUtils.isEmpty(param.get("num"))) {
                    ps.setNull(5, Types.INTEGER);
                } else {
                    ps.setInt(5, (int) Double.parseDouble(param.get("num")));
                }

                if (StringUtils.isEmpty(param.get("pole_material"))) {
                    ps.setNull(6, Types.INTEGER);
                } else {
                    ps.setInt(6, (int) Double.parseDouble(param.get("pole_material")));
                }

//                if (voltage_grade.equals("101")) {//高压
                if (StringUtils.isEmpty(param.get("high_wire_type"))) {
                    ps.setNull(7, Types.INTEGER);
                } else {
                    ps.setInt(7, (int) Double.parseDouble(param.get("high_wire_type")));
                }

                if (StringUtils.isEmpty(param.get("high_wire_model"))) {
                    ps.setNull(8, Types.BIGINT);
                } else {
                    ps.setLong(8, (long) Double.parseDouble(param.get("high_wire_model")));
                }

                if (StringUtils.isEmpty(param.get("high_wire_times"))) {
                    ps.setNull(9, Types.INTEGER);
                } else {
                    ps.setInt(9, (int) Double.parseDouble(param.get("high_wire_times")));
                }
//                } else {//低压
                if (StringUtils.isEmpty(param.get("low_wire_type"))) {
                    ps.setNull(10, Types.INTEGER);
                } else {
                    ps.setInt(10, (int) Double.parseDouble(param.get("low_wire_type")));
                }

                if (StringUtils.isEmpty(param.get("low_wire_model"))) {
                    ps.setNull(11, Types.BIGINT);
                } else {
                    ps.setLong(11, (long) Double.parseDouble(param.get("low_wire_model")));
                }

                if (StringUtils.isEmpty(param.get("low_wire_times"))) {
                    ps.setNull(12, Types.INTEGER);
                } else {
                    ps.setInt(12, (int) Double.parseDouble(param.get("low_wire_times")));
                }
//                }


                if (StringUtils.isEmpty(param.get("rank_way"))) {
                    ps.setNull(13, Types.INTEGER);
                } else {
                    ps.setInt(13, (int) Double.parseDouble(param.get("rank_way")));
                }

                if (StringUtils.isEmpty(param.get("pole_device"))) {
                    ps.setNull(14, Types.INTEGER);
                } else {
                    ps.setInt(14, (int) Double.parseDouble(param.get("pole_device")));
                }

                if (StringUtils.isEmpty(param.get("corner"))) {
                    ps.setNull(15, Types.DECIMAL);
                } else {
                    ps.setBigDecimal(15, new BigDecimal(param.get("corner")));
                }
                if (StringUtils.isEmpty(param.get("span"))) {
                    ps.setNull(16, Types.FLOAT);
                } else {
                    ps.setFloat(16, Float.parseFloat(param.get("span")));
                }
                if (StringUtils.isEmpty(param.get("voltage_type"))) {
                    ps.setNull(17, Types.INTEGER);
                } else {
                    ps.setInt(17, Integer.parseInt(param.get("voltage_type")));
                }
                if (StringUtils.isEmpty(param.get("laxian"))) {
                    ps.setNull(18, Types.INTEGER);
                } else {
                    ps.setInt(18, Integer.parseInt(param.get("laxian")));
                }
                return ps;
            }
        }, key);
        long id = key.getKey().longValue();
        return id;
    }

    /**
     * 下户点
     *
     * @param param 下户点 属性
     * @return
     */
    @Override
    public Long addUserPoint(final Map<String, String> param) {
        final String sql = "INSERT INTO a_user_point(all_user,user_name,user_num) VALUES(?,?,?)";
        KeyHolder key = new GeneratedKeyHolder();
        getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, param.get("all_user"));
                ps.setString(2, param.get("user_name"));
                ps.setInt(3, (int) Double.parseDouble(param.get("user_num")));
                return ps;
            }
        }, key);
        long id = key.getKey().longValue();
        return id;
    }

    /**
     * 户表箱
     *
     * @param param 户表箱 属性
     * @return
     */
    @Override
    public Long addUserElecbox(final Map<String, String> param) {
        final String sql = "INSERT INTO a_user_elecbox(user_point,digit,address,cjq_code,old_code,ip_address,new_code) VALUES(?,?,?,?,?,?,?)";
        KeyHolder key = new GeneratedKeyHolder();
        getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, param.get("user_point"));
                ps.setInt(2, (int) Double.parseDouble(param.get("digit")));
                ps.setString(3,param.get("address"));
                ps.setString(4,param.get("cjq_code"));
                ps.setString(5,param.get("old_code"));
                ps.setString(6,param.get("ip_address"));
                ps.setString(7,param.get("new_code"));
                return ps;
            }
        }, key);
        long id = key.getKey().longValue();
        return id;
    }

    /**
     * 植被
     *
     * @param param 植被 属性
     * @return
     */
    @Override
    public Long addVegetation(final Map<String, String> param) {
        final String sql = "INSERT INTO f_vegetation(vegetation_type,height,angle,fell_num) VALUES(?,?,?,?)";
        KeyHolder key = new GeneratedKeyHolder();
        getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, param.get("vegetation_type"));
                ps.setFloat(2, Float.parseFloat(param.get("height")));
                ps.setBigDecimal(3, new BigDecimal(param.get("angle")));
                ps.setInt(4, (int) Double.parseDouble(param.get("fell_num")));
                return ps;
            }
        }, key);
        long id = key.getKey().longValue();
        return id;
    }

    /**
     * 房屋
     *
     * @param param 房屋参数
     * @return
     */
    @Override
    public Long addHouses(final Map<String, String> param) {
        final String sql = "INSERT INTO f_houses(width,height,horizon_distance,angle) VALUES(?,?,?,?)";
        KeyHolder key = new GeneratedKeyHolder();
        getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setFloat(1, Float.parseFloat(param.get("width")));
                ps.setFloat(2, Float.parseFloat(param.get("height")));
                ps.setFloat(3, Float.parseFloat(param.get("horizon_distance")));
                ps.setBigDecimal(4, new BigDecimal(param.get("angle")));
                return ps;
            }
        }, key);
        long id = key.getKey().longValue();
        return id;
    }

    /**
     * 通讯线
     *
     * @param param 通讯线属性
     * @return
     */
    @Override
    public Long addCommLine(final Map<String, String> param) {
        final String sql = "INSERT INTO f_comm_line(height,horizon_distance,angle) VALUES(?,?,?)";
        KeyHolder key = new GeneratedKeyHolder();
        getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setFloat(1, Float.parseFloat(param.get("height")));
                ps.setFloat(2, Float.parseFloat(param.get("horizon_distance")));
                ps.setBigDecimal(3, new BigDecimal(param.get("angle")));
                return ps;
            }
        }, key);
        long id = key.getKey().longValue();
        return id;
    }

    /**
     * 低压线路
     *
     * @param param 低压线路属性
     * @return
     */
    @Override
    public Long addLowLine(final Map<String, String> param) {
        final String sql = "INSERT INTO f_low_line(height,horizon_distance,angle) VALUES(?,?,?)";
        KeyHolder key = new GeneratedKeyHolder();
        getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setFloat(1, Float.parseFloat(param.get("height")));
                ps.setFloat(2, Float.parseFloat(param.get("horizon_distance")));
                ps.setBigDecimal(3, new BigDecimal(param.get("angle")));
                return ps;
            }
        }, key);
        long id = key.getKey().longValue();
        return id;
    }

    /**
     * 沟河流
     *
     * @param param 沟河流属性
     * @return
     */
    @Override
    public Long addRivers(final Map<String, String> param) {
        final String sql = "INSERT INTO f_rivers(width,horizon_distance,relative_height,vertical_distance) VALUES(?,?,?,?)";
        KeyHolder key = new GeneratedKeyHolder();
        getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setFloat(1, Float.parseFloat(param.get("width")));
                ps.setFloat(2, Float.parseFloat(param.get("horizon_distance")));
                ps.setFloat(3, Float.parseFloat(param.get("relative_height")));
                ps.setFloat(4, Float.parseFloat(param.get("vertical_distance")));
                return ps;
            }
        }, key);
        long id = key.getKey().longValue();
        return id;
    }

    /**
     * 道路
     *
     * @param param 道路属性
     * @return
     */
    @Override
    public Long addRoad(final Map<String, String> param) {
        final String sql = "INSERT INTO f_road(road_type,width,horizon_distance,dispersion) VALUES(?,?,?,?)";
        KeyHolder key = new GeneratedKeyHolder();
        getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setInt(1, (int) Double.parseDouble(param.get("road_type")));
                ps.setFloat(2, Float.parseFloat(param.get("width")));
                ps.setFloat(3, Float.parseFloat(param.get("horizon_distance")));
                ps.setFloat(4, Float.parseFloat(param.get("dispersion")));
                return ps;
            }
        }, key);
        long id = key.getKey().longValue();
        return id;
    }

    /**
     * 高压线路
     *
     * @param param 高压线路属性
     * @return
     */
    @Override
    public Long addHighLine(final Map<String, String> param) {
        final String sql = "INSERT INTO f_high_line(height,horizon_distance,angle,dispersion) VALUES(?,?,?,?)";
        KeyHolder key = new GeneratedKeyHolder();
        getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

                ps.setFloat(1, Float.parseFloat(param.get("height")));
                ps.setFloat(2, Float.parseFloat(param.get("horizon_distance")));
                ps.setBigDecimal(3, new BigDecimal(param.get("angle")));
                ps.setFloat(4, Float.parseFloat(param.get("dispersion")));
                return ps;
            }
        }, key);
        long id = key.getKey().longValue();
        return id;
    }

    /**
     * 踏勘点信息继续设置
     *
     * @param exploration_id 踏勘点id
     */
    @Override
    public void conAddExploration(Long exploration_id) {

    }

    /**
     * 获取定位点上级定位点
     *
     * @param exploration_id 获取踏勘点id
     */
    @Override
    public Map<String, Object> beforeExp(Long exploration_id) {
        String sql = "select b.number,b.exploration_id as before_exp_id,a.distance,a.exploration_id,b.lng,b.lat" +
                " from p_exploration a" +
                " left join p_exploration b" +
                " on a.before_exp = b.exploration_id" +
                " where a.exploration_id = ? and b.deleted = 0 and a.deleted = 0";
        return findOneForJdbc(sql, new Object[]{exploration_id});
    }

    /**
     * 判断踏勘点是否存在
     *
     * @param exploration_id ptType 定位点11 /  特征点12
     * @return
     */
    @Override
    public boolean isExistFeature(Long exploration_id, int ptType) {
        boolean b = false;
        String sql = "select exploration_id from p_exploration exp  " +
                " LEFT JOIN p_pt pt on exp.pt_id=pt.pt_id  " +
                " where exploration_id = ?  " +
                "and pt.pt_type = ?   " +
                "and exp.deleted = 0 and pt.is_deleted = 0  ";
        List res = getJdbcTemplate().queryForList(sql, new Object[]{exploration_id, ptType});
        if (null != res && res.size() > 0) {
            b = true;
        }
        return b;
    }

    /**
     * 判断特征点是否已经关联
     *
     * @param featureId
     * @return
     */
    @Override
    public boolean isCorrFeature(Long featureId) {
        boolean b = false;
        String sql = "select * from p_channel where feature_id = ? ";
        List res = getJdbcTemplate().queryForList(sql, new Object[]{featureId});
        if (null != res && res.size() > 0) {
            b = true;
        }
        return b;
    }

    /**
     * 通过通道id获取特征点信息
     *
     * @param channelID
     * @return
     */
    @Override
    public Map<String, Object> explorBaseInfoByChannelID(Long channelID) {
        StringBuffer sql = new StringBuffer();
        sql.append("select * from p_channel pc ");
        sql.append("inner join  p_exploration pe ");
        sql.append("on pc.feature_id = pe.exploration_id ");
        sql.append("where channel_id = ?");
        return findOneObject(sql.toString(), new Object[]{channelID});
    }

    /**
     * 更新踏勘典设
     *
     * @param codesetId
     * @param explorationId
     */
    @Override
    public void upCodeSet(Long codesetId, Long explorationId) {
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE p_exploration  ");
        sql.append("SET codeset_id = ?  ");
        sql.append("where exploration_id = ? ");
        getJdbcTemplate().update(sql.toString(), new Object[]{codesetId, explorationId});
    }

    /**
     * 获取台区
     *
     * @param stage_id
     * @return
     */
    @Override
    public List<Map<String, Object>> transformerList(Long stage_id, int classify) {
        StringBuffer sql = new StringBuffer();
        sql.append("select a.exploration_id,b.`name`,a.number ,a.exploration_type ");
        sql.append("from p_exploration a ");
        sql.append("inner join a_transformer b ");
        sql.append("on a.pt_objectid = b.id ");
        sql.append("where a.stage_id = ? ");
        sql.append("and a.pt_id = 100002 ");
        sql.append("and a.deleted = 0 ");
        sql.append("and a.classify = ? ");
        return getJdbcTemplate().queryForList(sql.toString(), stage_id, classify);
    }

    /**
     * number、 before_exp、exploration_type、pt_id、lng、lat、corner、alt、distance、location_way
     *
     * @param param
     * @param explorationId
     */
    @Override
    public void upExploration(Map<String, Object> param, Long explorationId) {

    }

}
